﻿using MgSoft.Excel.Model;
using MgSoft.Excel.Model.Excel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net.Http.Headers;

namespace MgSoft.Excel.Util
{
    public static class ExceltUtil
    {
        public static IWorkbook ReadFromExcelFile(string filePath, ExcelType excelType)
        {
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                return ReadFromExcelStream(fs, excelType);
            }

        }

        public static IWorkbook ReadFromExcelStream(Stream stream, ExcelType excelType)
        {
            IWorkbook wk = null;
            stream.Position = 0;
            switch (excelType)
            {
                case ExcelType.xls:
                    //把xls文件中的数据写入wk中

                    wk = new HSSFWorkbook(stream);
                    break;
                case ExcelType.xlsx:
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(stream);
                    break;
            }
            return wk;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sourceFilePath"></param>
        /// <param name="targetFilePath"></param>
        /// <param name="excelType"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="mgCellList"></param>
        public static void WriteToNewExcel(string sourceFilePath, string targetFilePath, ExcelType excelType, int sheetIndex, List<MgCell> mgCellList)
        {
            var sourceWorkBook = ReadFromExcelFile(sourceFilePath, excelType);
            //对象工厂，根据Excel的类型，构建不同的实现类
            objectFactory(out IWorkbook newWorkBook, out ISheet oldSheet, out ISheet newSheet, excelType, sourceWorkBook, sheetIndex);
            var cellStyle = getCellStyles(newWorkBook);

            var columnIndexList = mgCellList.Select(x => new { x.ColumnIndex }).GroupBy(p => p.ColumnIndex).ToList();
            //将旧表的值复制给新表
            copyExcel(oldSheet, newSheet);
            //给新表的Sheet的名称赋值
            newWorkBook.SetSheetName(0, oldSheet.SheetName);

            foreach (var item in mgCellList)
            {
                IRow workRow = null;
                //workRowFactory(out IRow workRow, newSheet, item.RowIndex, excelType);
                workRowFactory(out workRow, newSheet, item.RowIndex, excelType);
                setErrorInfo(workRow, item, newWorkBook, cellStyle);
            }

            using (var wook = File.OpenWrite(targetFilePath))
            {
                newWorkBook.Write(wook);
            }
        }

        private static ICellStyle getCellStyles(IWorkbook workBook)
        {
            //创建一个字体颜色
            IFont font = workBook.CreateFont();
            //红色
            font.Color = IndexedColors.Red.Index;
            //样式
            ICellStyle style = workBook.CreateCellStyle();
            //给样式设置字体
            style.SetFont(font);

            return style;
        }

        private static void objectFactory(out IWorkbook newWorkBook, out ISheet oldSheet, out ISheet newSheet, ExcelType excelType, IWorkbook sourceWorkBook, int sheetIndex)
        {
            switch (excelType)
            {
                case ExcelType.xlsx:
                    newWorkBook = new XSSFWorkbook();
                    oldSheet = sourceWorkBook.GetSheetAt(sheetIndex) as XSSFSheet;
                    newSheet = newWorkBook.CreateSheet() as XSSFSheet;
                    break;
                default://ExcelType.xls
                    newWorkBook = new HSSFWorkbook();
                    oldSheet = sourceWorkBook.GetSheetAt(sheetIndex) as HSSFSheet;
                    newSheet = newWorkBook.CreateSheet() as HSSFSheet;
                    break;
            }
        }

        private static void workRowFactory(out IRow workRow, ISheet sheet, int rowIndex, ExcelType excelType)
        {
            switch (excelType)
            {

                case ExcelType.xlsx:
                    workRow = sheet.GetRow(rowIndex) as XSSFRow;
                    workRow = workRow is null ? sheet.CreateRow(rowIndex) as XSSFRow : workRow;
                    break;
                default://ExcelType.xls:
                    workRow = sheet.GetRow(rowIndex) as HSSFRow;
                    workRow = workRow is null ? sheet.CreateRow(rowIndex) as HSSFRow : workRow;
                    break;
            }
        }

        private static void setErrorInfo(IRow iRow, MgCell mgCell, IWorkbook workBook, ICellStyle cellStyle)
        {
            //addCell_Row(headerRow, item.RowIndex, excelType);
            var cell = iRow.GetCell(mgCell.ColumnIndex);
            if (cell is null) iRow.CreateCell(mgCell.ColumnIndex);
            setCellValue(iRow, mgCell.ColumnIndex, mgCell.Value.ToString());
            setFontColor_Row(iRow, workBook, cellStyle);
        }


        private static void copyExcel(ISheet sSheet, ISheet tSheet)
        {
            try
            {
                for (int i = 0; i < sSheet.LastRowNum; i++)
                {
                    var sHeaderRow = sSheet.GetRow(i);

                    tSheet.CreateRow(i);
                    var tHeaderRow = tSheet.GetRow(i);
                    if (sHeaderRow == null || sHeaderRow.Cells == null)
                    {
                        continue;
                    }
                    for (int x = 0; x < sHeaderRow.LastCellNum; x++)
                    {
                        var tempCell = sHeaderRow.GetCell(x);
                        var targetCell = tHeaderRow.CreateCell(x);  
                        if (tempCell == null) continue;
                        switch (tempCell.CellType)
                        {
                            case CellType.Unknown:
                                break;
                            case CellType.Numeric:
                                var format = tempCell.CellStyle.DataFormat;
                                if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || format == 22)
                                {
                                    targetCell.SetCellValue(tempCell.DateCellValue.Date.ToString("yyyy/MM/dd HH:mm:ss"));
                                }
                                else
                                {
                                    targetCell.SetCellValue(tempCell.NumericCellValue);
                                }
                                break; 
                            case CellType.String:
                                targetCell.SetCellValue(tempCell.StringCellValue);
                                break;
                            case CellType.Formula:
                                targetCell.SetCellValue(tempCell.CellFormula);
                                break;
                            case CellType.Blank:
                                break;
                            case CellType.Boolean:
                                targetCell.SetCellValue(tempCell.BooleanCellValue);
                                break;
                            case CellType.Error:
                                break;
                            default:
                                break;
                        }
                    }

                }
            }
            catch (Exception e)
            {

                throw new Exception($"copyExcel异常：{e.Message}");
            }
        }

        /// <summary>
        /// 清空某一列的数据
        /// </summary>
        /// <param name="hSSFSheet"></param>
        /// <param name="collumnIndex"></param>
        private static void removeDataByRow(ISheet sheet, int collumnIndex, ExcelType excelType)
        {
            //var hSSFSheet = sheet as HSSFSheet;
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                IRow headerRow = sheet.GetRow(i);
                ICell cell = headerRow.GetCell(collumnIndex);

                if (cell != null)
                {
                    cell.SetCellValue("");
                }
            }
        }
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="collumnIndex"></param>
        /// <param name="value"></param>
        private static void setCellValue(IRow row, int collumnIndex, string value)
        {
            ICell cell = row.GetCell(collumnIndex);
            if (cell != null)
            {
                cell.SetCellValue(value);
            }
            else
            {
                row.CreateCell(collumnIndex);
                cell = row.GetCell(collumnIndex);
                cell.SetCellValue(value);
            }
        }
        /// <summary>
        /// 给某一行添加空单元格（cell）
        /// </summary>
        /// <param name="row"></param>
        /// <param name="toCollumnIndex"></param>
        /// <param name="excelType"></param>
        private static void addCell_Row(IRow row, int toCollumnIndex, ExcelType excelType)
        {
            for (int i = row.Cells.Count - 1; i <= toCollumnIndex; i++)
            {
                var tempCell = row.GetCell(i);
                if (tempCell == null)
                {
                    row.CreateCell(i);
                }
            }
        }
        /// <summary>
        /// 设置字体颜色（行）
        /// </summary>
        /// <param name="row"></param>
        /// <param name="wb"></param>
        private static void setFontColor_Row(IRow row, IWorkbook wb, ICellStyle cellStyle)
        {
            foreach (var cellItem in row)
            {
                cellItem.CellStyle = cellStyle;
            }
        }
    }
}
